package com.base.components.common.db.dialect;

import com.github.pagehelper.Page;
import com.github.pagehelper.dialect.ReplaceSql;
import com.github.pagehelper.dialect.replace.SimpleWithNolockReplaceSql;
import com.github.pagehelper.parser.SqlServerParser;
import com.google.common.collect.LinkedHashMultimap;
import com.google.common.collect.Multimap;

import java.util.Collection;
import java.util.Map;

/**
 * PaginationDialect
 *
 * @author <a href="drakelee1221@gmail.com">LiGeng</a>
 * @version v1.0.0
 * @date 2019-01-23 14:43
 */
public class PaginationDialect {
  private static final Multimap<PageSqlBuilder, String> DELEGATES = LinkedHashMultimap.create();
  static {
    PageSqlBuilder db2 = (sql, page) ->
      "SELECT * FROM (SELECT TMP_PAGE.*,ROWNUMBER() OVER() AS ROW_ID FROM ( " + sql
        + " ) AS TMP_PAGE) TMP_PAGE WHERE ROW_ID BETWEEN " + (page.getStartRow() + 1) + " AND " + page.getEndRow();
    DELEGATES.put(db2, "db2");

    PageSqlBuilder hsqldb = (sql, page) -> {
      StringBuilder sqlBuilder = new StringBuilder(sql);
      if (page.getPageSize() > 0) {
        sqlBuilder.append(" LIMIT ").append(page.getPageSize()).append(" ");
      }
      if (page.getStartRow() > 0) {
        sqlBuilder.append(" OFFSET ").append(page.getStartRow()).append(" ");
      }
      return sqlBuilder.toString();
    };
    DELEGATES.put(hsqldb, "hsqldb");
    DELEGATES.put(hsqldb, "h2");
    DELEGATES.put(hsqldb, "postgresql");
    DELEGATES.put(hsqldb, "phoenix");

    PageSqlBuilder informix = (sql, page) -> {
      if(page.getStartRow() <= 0 && page.getPageSize() <= 0){
        return sql;
      }
      StringBuilder sqlBuilder = new StringBuilder("SELECT ");
      if (page.getStartRow() > 0) {
        sqlBuilder.append(" SKIP ").append(page.getStartRow()).append(" ");
      }
      if (page.getPageSize() > 0) {
        sqlBuilder.append(" FIRST ").append(page.getPageSize()).append(" ");
      }
      return sqlBuilder.append(" * FROM ( ").append(sql).append(" ) TEMP_T ").toString();
    };
    DELEGATES.put(informix, "informix");
    DELEGATES.put(informix, "informix-sqli");

    PageSqlBuilder mysql = (sql, page) -> {
      StringBuilder sqlBuilder = new StringBuilder(sql);
      if (page.getStartRow() == 0) {
        sqlBuilder.append(" LIMIT ").append(page.getPageSize()).append(" ");
      } else {
        sqlBuilder.append(" LIMIT ").append(page.getStartRow()).append(", ").append(page.getPageSize()).append(" ");
      }
      return sqlBuilder.toString();
    };
    DELEGATES.put(mysql, "mysql");
    DELEGATES.put(mysql, "mariadb");
    DELEGATES.put(mysql, "sqlite");

    PageSqlBuilder oracle = (sql, page) ->
      "SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM ROW_ID FROM ( " + sql + " ) TMP_PAGE)"
        + " WHERE ROW_ID <= " + page.getEndRow() + " AND ROW_ID > " + page.getStartRow();
    DELEGATES.put(oracle, "oracle");
    DELEGATES.put(oracle, "dm");
    DELEGATES.put(oracle, "edb");

    PageSqlBuilder sqlserver2012 = (sql, page) ->
      sql + " OFFSET " + page.getStartRow() + " ROWS FETCH NEXT " + page.getPageSize() + " ROWS ONLY ";
    DELEGATES.put(sqlserver2012, "sqlserver2012");
    DELEGATES.put(sqlserver2012, "derby");

    PageSqlBuilder sqlserver = new PageSqlBuilder() {
      private SqlServerParser pageSql = new SqlServerParser();
      private ReplaceSql replaceSql = new SimpleWithNolockReplaceSql();
      @Override
      public String getPageSql(String sql, Page page) {
        sql = replaceSql.replace(sql);
        sql = pageSql.convertToPageSql(sql, null, null);
        sql = replaceSql.restore(sql);
        sql = sql.replace(String.valueOf(Long.MIN_VALUE), String.valueOf(page.getStartRow()));
        sql = sql.replace(String.valueOf(Long.MAX_VALUE), String.valueOf(page.getPageSize()));
        return sql;
      }
    };
    DELEGATES.put(sqlserver, "sqlserver");
  }

  public static PageSqlBuilder getPageSqlBuilder(String dbName){
    for (Map.Entry<PageSqlBuilder, Collection<String>> entry : DELEGATES.asMap().entrySet()) {
      if (entry.getValue().contains(dbName)) {
        return entry.getKey();
      }
    }
    return null;
  }
  public interface PageSqlBuilder{
    String getPageSql(String sql, Page page);
  }
}
